﻿/* ***********************************************
 * Author		:  kingthy
 * Email		:  kingthy@gmail.com
 * Description	:  SqlDataPagingReader
 *
 * ***********************************************/

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Data.Common;

namespace KT.Framework.Database
{
    /// <summary>
    /// 针对SQL Server 2005或以上版本而实现的DbDataPagingReader. 
    /// </summary>
    public class SqlDataPagingReader
        : DbDataPagingReader
    {
        /// <summary>
        /// 根据SqlConnection对象实例化
        /// </summary>
        /// <param name="sqlConnection">System.Data.SqlClient.SqlConnection数据连接对象</param>
        public SqlDataPagingReader(SqlConnection sqlConnection)
            : base(sqlConnection)
        { }
        /// <summary>
        /// 根据数据库帮助对象实例化
        /// </summary>
        /// <param name="dbHelper"></param>
        public SqlDataPagingReader(DbHelper dbHelper) : base(dbHelper) { }
        /// <summary>
        /// 获取当前页的数据并返回一个数据对象列表
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public override List<T> ReadAsDbObjectList<T>()
        {
            try
            {
                this.BeginExecute();
                this.InitPageCount();
                if (this.RecordCount > 0)
                {
                    string sql = this.GetQueryCommand();
                    using (var command = this.DbHelper.CreateDbCommandWrapped(sql.ToString(), this.Condition.Parameters))
                    {
                        return command.ExecuteDbObjectList<T>();
                    }
                }
                else
                {
                    return new List<T>();
                }
            }
            finally
            {
                this.EndExecute();
            }
        }

        /// <summary>
        /// 获取当前页的数据并返回一个数据表
        /// </summary>
        /// <returns></returns>
        public override System.Data.DataTable ReadAsDataTable()
        {
            try
            {
                this.BeginExecute();
                this.InitPageCount();
                if (this.RecordCount > 0)
                {
                    string sql = this.GetQueryCommand();
                    using (var command = this.DbHelper.CreateDbCommandWrapped(sql.ToString(), this.Condition.Parameters))
                    {
                        DataSet ds = new DataSet();
                        using (var adapter = this.DbHelper.CreateDbDataAdapter(command.Command))
                        {
                            command.FillDataSet(adapter, ds);
                            return ds.Tables.Count > 0 ? ds.Tables[0] : null;
                        }
                    }
                }
                else
                {
                    return null;
                }
            }
            finally
            {
                this.EndExecute();
            }
        }
        #region 获取查询的命令语句
        /// <summary>
        /// 获取查询的命令语句
        /// </summary>
        /// <returns></returns>
        protected virtual string GetQueryCommand()
        {
            if (string.IsNullOrEmpty(this.OrderBy)) throw new ArgumentException("OrderBy");

            StringBuilder sql = new StringBuilder();
            int s = (this.PageNumber - 1) * this.PageSize + 1;
            int e = this.PageNumber * this.PageSize;

            string orderBy = string.IsNullOrEmpty(this.OrderBy) ? "GETDATE() ASC" : this.OrderBy;

            sql.Append("SELECT * FROM (");
            sql.AppendFormat("SELECT ROW_NUMBER() OVER (ORDER BY {0}) AS _ROW_NUMBER, * FROM (", orderBy);
            sql.Append(this.Select);
            if (this.Condition.HasCondition)
            {
                sql.Append(" ");
                sql.Append(this.Condition.ToString());
            }
            sql.Append(") [_tb1]) [_tb2]");
            sql.AppendFormat(" WHERE [_tb2].[_ROW_NUMBER] BETWEEN {0} AND {1} ORDER BY [_tb2].[_ROW_NUMBER] ASC", s, e);
            return sql.ToString();
        }
        #endregion
    }
}
